if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWorkflowExecutionDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWorkflowExecutionDetails]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/************************************************************************************************          
* Name:  [GetWorkflowExecutionDetails]                                                                    
* Purpose: This stored procedure gets the email generated(scrapped form chunk of data) having the         
*     passed in executionId         
*          
*            
* PARAMETERS            
* Name   Description          
* ------------------------- -----------------------------          
* @ExecutionId    Workflow execution Id          
*          
************************************************************************************************          
*Call: [GetWorkflowExecutionDetails] 6          
************************************************************************************************/  
CREATE procedure [dbo].[GetWorkflowExecutionDetails]  
(          
 @executionId int           
)   
as          
begin          
set nocount on    
    
DECLARE @hourDiff int         
 set @hourDiff = (select datediff(hour, getdate(), getutcdate())) * -1            
 DECLARE @Email TABLE (                        
    [ExecutionDetailsId] INT,                      
    [EmailDescription] nvarchar(max)                      
    )                        
    Insert Into @Email([ExecutionDetailsId],[EmailDescription]) EXEC [dbo].[GetWFEMailDecription] @executionId         
     
--  If exists (Select 1 from @Email)    
--     BEGIN               
   Select Distinct swd.[ActionName],            
   DATEADD(hh,@hourDiff,swd.[StartedOn]) as StartedOn,                         
   swd.[outPut],   
   CASE WHEN swd.[ActionType] = 'Approval' THEN 'Review' ELSE swd.[ActionType] END as [ActionType],                          
   cast(swl.[Description] as varchar(1000)) as [ActivityDescription],           
   rtrim(ltrim(adp.LastName)) +', '+rtrim(ltrim(adp.FirstName)) as FullName,       
   fwku.UserName,            
   DATEADD(hh,@hourDiff,swd.[LastUpdatedDateTime])as[ExecutionEndDate],        
   swl.[Comments] as [Comments],        
   email.EmailDescription        
             
   from swexecutionDetails  swd            
   left join swactivitylist swl             
   On swd.ActionName = swl.ActivityName              
   and swl.ExecutionId = swd.ExecutionId            
   left join swvirtualUser swvu            
   On swvu.Id = swl.VirtualuserId            
   Left join fwkDomainUser fwku            
   On fwku.Id= swvu.UserId --adp.FwkDomainUserId        
   Left join admperson adp            
   On adp.FwkDomainUserId = Fwku.Id         
   left join @Email email        
   On email.ExecutionDetailsId = swd.[Id]        
   where swd.executionid=@executionId 
   And swd.ActionType not in ('Synchronize','Review','Approval')  	        
  UNION
   Select Distinct swd.[ActionName],            
   DATEADD(hh,@hourDiff,swd.[StartedOn]) as StartedOn,                         
   swd.[outPut],   
   CASE WHEN swd.[ActionType] = 'Approval' THEN 'Review' ELSE swd.[ActionType] END as [ActionType],                          
   cast(swl.[Description] as varchar(1000)) as [ActivityDescription],            
   rtrim(ltrim(adp.LastName)) +', '+rtrim(ltrim(adp.FirstName)) as FullName,       
   fwku.UserName,            
   DATEADD(hh,@hourDiff,swd.[LastUpdatedDateTime])as[ExecutionEndDate],        
   swl.[Comments] as [Comments],        
   email.EmailDescription        
             
   from swexecutionDetails  swd            
   left join swactivitylist swl             
   On swd.ActionName = swl.ActivityName            
   and swl.ExecutionId = swd.ExecutionId            
   left join swvirtualUser swvu            
   On swvu.Id = swl.VirtualuserId            
   Left join fwkDomainUser fwku            
   On fwku.Id= swvu.UserId --adp.FwkDomainUserId        
   Left join admperson adp            
   On adp.FwkDomainUserId = Fwku.Id         
   left join @Email email        
   On email.ExecutionDetailsId = swd.[Id]    
   AND (PATINDEX('%'+fwku.userName+'%',email.EmailDescription) <> 0)         
   where swd.executionid= @executionId       
   And swd.[actiontype] = 'approval'
   And ((swd.[ActionName] in (Select distinct(actionname) from 
							swexecutionDetails where actiontype = 'approval' 
							and actionName not in('SSReview','SScertification','animalsSScertification')))
   OR (PATINDEX('%'+fwku.userName+'%',email.EmailDescription) <> 0))  
   order by swd.[StartedOn] 
END  
